# -*- coding: utf-8 -*-
from openerp.osv import osv, fields
import datetime
import pytz
import random
from openerp.tools.translate import _
from openerp import models, SUPERUSER_ID


# 卡券提货缓存统计表
class product_card_delivery_stat_day(osv.osv):
    _name = "product.card.delivery.stat.day"
    _description = u'卡券提货统计'
    _columns = {
        'date': fields.date(u'日期'),  # 定时任务，从track和订单表冗余到这个统计表
        'card_so_id': fields.many2one('sale.order', u'卡券制作订单', required=False),
        'product_id': fields.many2one('product.product', u'产品', required=True),
        'product_name': fields.char(u'产品名称'),
        'quantity': fields.integer(u'产品数量'),
        'state': fields.char(u'状态类型'),  # 状态类型（A已发货、B已换货、C已锁未发、D已提未锁、E预估未提、F可用库存, G已采未入）
    }

    # 刷新某天的A已发货、B已换货数量
    def summary(self, cr, uid, date=None, context=None):
        if not date:
            # 取昨天的确认订单列表
            date = (datetime.datetime.now() - datetime.timedelta(days=1))
            date = date.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
            date = date.strftime("%Y-%m-%d")

        # SELECT date_trunc('day', sp.date_done) AS date, ad.card_so_id, sol.product_id,
        # sol.name AS product_name, sum(sol.product_uom_qty) AS quantity, 'A已发货' AS state
        # FROM apply_delivery ad
        # INNER JOIN sale_order so ON ad.so_id=so.id
        # INNER JOIN sale_order_line sol ON sol.order_id=so.id
        # INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        # WHERE ad.is_shipping = TRUE AND ad.is_canceled=FALSE AND DATE(sp.date_done) = %s
        # GROUP BY date_trunc('day', sp.date_done), ad.card_so_id, sol.product_id, sol.name
        cr.execute('''
        SELECT date_trunc('day', sp.date_done) AS date, so.card_so_id, sm.product_id,
        sm.name AS product_name, sum(CASE WHEN sp.picking_type_id=1 THEN 0-sm.product_qty ELSE sm.product_qty END)
         AS quantity, 'A已发货' AS state
        FROM sale_order so
        INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        INNER JOIN stock_move sm ON sm.picking_id=sp.id
        WHERE so.shipment_completion = TRUE AND so.order_type_id=4 AND DATE(sp.date_done) = %s
        GROUP BY date_trunc('day', sp.date_done), so.card_so_id, sm.product_id, sm.name
        UNION

        SELECT date_trunc('day', so.write_date) AS date, ad.card_so_id, sm.product_id,
        sm.name AS product_name, sum(CASE WHEN sp.picking_type_id=1 THEN 0-sm.product_qty ELSE sm.product_qty END)
         AS quantity, 'B已换货' AS state
        FROM apply_delivery ad
        INNER JOIN sale_order so ON ad.exchange_so_id=so.id
        INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        INNER JOIN stock_move sm ON sm.picking_id=sp.id
        WHERE ad.is_shipping = TRUE AND ad.is_canceled=FALSE AND DATE(sp.write_date) = %s
        GROUP BY date_trunc('day', so.write_date), ad.card_so_id, sm.product_id, sm.name
            ''', (date, date))

        rows = cr.dictfetchall()
        # 防止万一有重复的记录，先删除
        cr.execute("DELETE FROM product_card_delivery_stat_day WHERE date=%s AND state IN ('A已发货', 'B已换货')", (date,))
        for card_row in rows:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(%s, %s, %s, %s, %s, %s)",
                (card_row['date'], card_row['card_so_id'], card_row['product_id'], card_row['product_name'],
                 card_row['quantity'], card_row['state']))
        return True

    # 刷新当天订单action
    def summary_today(self, cr, uid, context=None):
        today = datetime.datetime.now()
        today = today.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
        today = today.strftime("%Y-%m-%d")
        self.summary(cr, uid, today)
        return True

    # 刷新所有已完成不再变化的记录（已发货、已换货）
    def summary_all(self, cr, uid, context=None):
        cr.execute(u"""
        SELECT date_trunc('day', sp.date_done) AS date, so.card_so_id, sm.product_id,
        sm.name AS product_name, sum(CASE WHEN sp.picking_type_id=1 THEN 0-sm.product_qty ELSE sm.product_qty END)
         AS quantity, 'A已发货' AS state
        FROM sale_order so
        INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        INNER JOIN stock_move sm ON sm.picking_id=sp.id
        WHERE so.shipment_completion = TRUE AND so.order_type_id=4
        GROUP BY date_trunc('day', sp.date_done), so.card_so_id, sm.product_id, sm.name

        UNION

        SELECT date_trunc('day', sp.write_date) AS date, ad.card_so_id, sm.product_id,
        sm.name AS product_name, sum(
            CASE WHEN sp.picking_type_id=2 THEN
            sm.product_qty
            ELSE
            0 - sm.product_qty
            END
            ) AS quantity, 'B已换货' AS state
        FROM apply_delivery ad
        INNER JOIN sale_order so ON ad.exchange_so_id=so.id
        INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        INNER JOIN stock_move sm ON sm.picking_id=sp.id
        WHERE ad.is_shipping = TRUE AND ad.is_canceled=FALSE AND so.order_type_id=4
        GROUP BY date_trunc('day', sp.write_date), ad.card_so_id, sm.product_id, sm.name
            """)
        rows = cr.dictfetchall()

        # 先删除所有
        cr.execute(u"DELETE FROM product_card_delivery_stat_day WHERE state IN ('A已发货', 'B已换货')")
        for card_row in rows:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(%s, %s, %s, %s, %s, %s)",
                (card_row['date'], card_row['card_so_id'], card_row['product_id'], card_row['product_name'],
                 card_row['quantity'], card_row['state']))
        return True

    # 刷新C已锁未发、D已提未锁
    def refresh_possible_changed_qty(self, cr, uid, context=None):
        cr.execute(u"""
        SELECT date_trunc('day', so.write_date) AS date, ad.card_so_id, sm.product_id,
        sm.name AS product_name, sum(CASE WHEN sp.picking_type_id=1 THEN 0-sm.product_qty ELSE sm.product_qty END)
         AS quantity, 'C已锁未发' AS state
        FROM apply_delivery ad
        INNER JOIN sale_order so ON ad.so_id=so.id
        INNER JOIN stock_picking sp ON sp.so_id=so.id AND sp.state<>'cancel'
        INNER JOIN stock_move sm ON sm.picking_id=sp.id
        WHERE ad.is_shipping = FALSE AND ad.is_canceled=FALSE AND so.is_stock_reserved=TRUE AND so.order_type_id=4
        GROUP BY date_trunc('day', so.write_date), ad.card_so_id, sm.product_id, sm.name

        UNION

        SELECT ad.estimated_ship_date AS date, ad.card_so_id, sol.product_id,
        sol.name AS product_name, sum(sol.product_uom_qty) AS quantity, 'D已提未锁' AS state
        FROM apply_delivery ad
        INNER JOIN sale_order so ON ad.so_id=so.id
        INNER JOIN sale_order_line sol ON sol.order_id=so.id
        WHERE ad.is_shipping = FALSE AND ad.is_canceled=FALSE AND so.is_stock_reserved=FALSE
        GROUP BY ad.estimated_ship_date, ad.card_so_id, sol.product_id, sol.name
            """)
        rows = cr.dictfetchall()

        # 先删除所有
        cr.execute(u"DELETE FROM product_card_delivery_stat_day WHERE state IN ('C已锁未发', 'D已提未锁')")
        for card_row in rows:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(%s, %s, %s, %s, %s, %s)",
                (card_row['date'], card_row['card_so_id'], card_row['product_id'], card_row['product_name'],
                 card_row['quantity'], card_row['state']))
        return True

    # 刷新预计未提货数
    def refresh_future_exchange_qty(self, cr, uid, context=None):
        # 取得所有卡券订单ids
        sql = "SELECT id FROM sale_order WHERE is_card=TRUE AND " \
              " real_so_id IS NULL  AND state NOT IN ('cancel')"
        cr.execute(sql)
        order_id_rows = cr.dictfetchall()
        order_ids = []
        for order_id_row in order_id_rows:
            order_ids.append(order_id_row.get('id'))

        order_ids = list(set(order_ids))
        res = []
        for sale_order in self.pool.get('sale.order').browse(cr, uid, order_ids, context=context):
            socp_product_ids_map = {}
            # 总的已提货份数
            exchange_sum = 0
            virtual_exchange_sum = 0
            suite_exchange_count_map = {}  # 选项提货数map（防止相同选项重复相加）
            suite_exchange_virtual_number_map = {}  # 选项提货比率计算计算基数map(把0提货数量变成可计算比率的数字，比如+1)
            suite_exchange_rate_map = {}  # 选项提货比率
            have_zero_exchange_suite = False
            for socp_row in sale_order.sale_order_card_product_ids:
                card_exchange_count = socp_row.card_exchange_count
                suite_exchange_count_map.update({socp_row.suite: card_exchange_count / socp_row.product_qty})
                socp_product_ids_map.update({socp_row.id: socp_row.product_id.id})
                if card_exchange_count == 0:
                    have_zero_exchange_suite = True
                suite_exchange_virtual_number_map.update({socp_row.suite: card_exchange_count / socp_row.product_qty})

            # 虚拟提货数如果有0的提货数量，则都加上1
            if have_zero_exchange_suite:
                virtual_exchange_sum = 0
                for suite in suite_exchange_virtual_number_map:
                    suite_exchange_virtual_number_map.update({suite: suite_exchange_virtual_number_map.get(suite) + 1})

            # 计算总提货份数， 和虚拟提货总份数（用于计算提货比率）
            for suite in suite_exchange_count_map:
                exchange_sum += suite_exchange_count_map.get(suite)
                virtual_exchange_sum += suite_exchange_virtual_number_map.get(suite)

            # 剩余提货份数
            exchange_residue = sale_order.card_qty - exchange_sum

            # 计算比率
            for suite in suite_exchange_count_map:
                suite_exchange_rate_map.update(
                    {suite: 1.0 * suite_exchange_virtual_number_map.get(suite) / virtual_exchange_sum})

            last_suite = 0
            suite_residue_qty_sum = 0
            for socp_row in sale_order.sale_order_card_product_ids:
                # 当前选项提货比率
                suite_rate = suite_exchange_rate_map.get(socp_row.suite)

                product_id = socp_row.product_id.id
                # if not res.has_key(product_id):
                #     res[product_id] = 0
                qty = round(exchange_residue * suite_rate)
                if socp_row.suite != last_suite:
                    suite_residue_qty_sum += qty
                last_suite = socp_row.suite
                # 如果选项累加的剩余未提货数超过了某卡券总的剩余未提货数
                if suite_residue_qty_sum > exchange_residue:
                    qty = 0
                    suite_residue_qty_sum -= qty
                    if suite_residue_qty_sum + 2 <= exchange_residue:
                        qty = 2
                        suite_residue_qty_sum += qty
                    elif suite_residue_qty_sum + 1 <= exchange_residue:
                        qty = 1
                        suite_residue_qty_sum += qty
                res.append({'product_id': product_id, 'card_so_id': sale_order.id, 'qty': qty * socp_row.product_qty})

        # 先删除所有预估未提缓存
        cr.execute(u"DELETE FROM product_card_delivery_stat_day WHERE state IN ('E预估未提')")
        for qty_row in res:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(NULL, %s, %s, NULL, %s, %s)",
                (qty_row.get('card_so_id'), qty_row.get('product_id'), qty_row.get('qty'), u'E预估未提'))
        return True

    # 刷新可用库存
    def refresh_available_stock_qty(self, cr, uid, context=None):
        res = {}
        model_data_obj = self.pool.get('ir.model.data')
        card_exchange_order_id = model_data_obj.xmlid_to_res_id(
            cr, SUPERUSER_ID, 'dftg_ext.dftg_order_type_data_card_exchange_order')
        order_type_exchange = self.pool.get('order.type').browse(cr, uid, card_exchange_order_id, context=context)
        card_location_id = order_type_exchange.stock_location_id.id
        child_card_location_ids = self.pool.get('stock.location') \
            .get_child_location_ids(cr, uid, card_location_id, context=context)
        cr.execute("SELECT product_id, sum(qty) as stock_qty FROM stock_quant"
                   " WHERE (reservation_id IS NULL or reservation_id = -1 )AND location_id IN %s"
                   " GROUP BY product_id", (tuple(child_card_location_ids),))
        product_quant_rows = cr.dictfetchall()

        # 先删除所有库存缓存
        cr.execute(u"DELETE FROM product_card_delivery_stat_day WHERE state IN ('F可用库存')")

        for product_quant_row in product_quant_rows:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(NULL, NULL, %s, NULL, %s, %s)",
                (product_quant_row.get('product_id'), product_quant_row.get('stock_qty'), u'F可用库存'))
        return True

    # 刷新采购未入库数
    def refresh_purchase_incoming_qty(self, cr, uid, context=None):
        model_data_obj = self.pool.get('ir.model.data')
        card_exchange_order_id = model_data_obj.xmlid_to_res_id(
            cr, SUPERUSER_ID, 'dftg_ext.dftg_order_type_data_card_exchange_order')
        order_type_exchange = self.pool.get('order.type').browse(cr, uid, card_exchange_order_id, context=context)
        stock_location_id = order_type_exchange.stock_location_id.id
        # 取得所有卡券订单产品ids
        # sql = "SELECT DISTINCT sol.product_id FROM sale_order so INNER JOIN sale_order_line sol ON sol.order_id=so.id" \
        #       " WHERE so.is_card=TRUE"
        # cr.execute(sql)
        # product_id_rows = cr.dictfetchall()
        # product_ids = []
        # for product_id_row in product_id_rows:
        #     product_ids.append(product_id_row.get('product_id'))

        cr.execute("""SELECT pol.product_id, sum(
                          CASE WHEN pol.product_qty > 0 THEN
                            pol.product_qty - pol.incomed_qty
                          ELSE
                            pol.product_qty + pol.incomed_qty
                          END) AS incoming_qty
                        FROM purchase_order po
                        INNER JOIN purchase_order_line pol ON pol.order_id=po.id
                        WHERE po.location_id = %s AND abs(pol.product_qty) > pol.incomed_qty AND po.state<>'cancel'
                        GROUP BY pol.product_id
        """, (stock_location_id,))  # tuple(product_ids)
        purchase_incoming_rows = cr.dictfetchall()

        # 先删除所有库存缓存
        cr.execute(u"DELETE FROM product_card_delivery_stat_day WHERE state IN ('G已采未入')")

        for purchase_incoming_row in purchase_incoming_rows:
            cr.execute(
                "INSERT INTO product_card_delivery_stat_day(date, card_so_id, product_id, product_name, quantity, state)"
                " VALUES(NULL, NULL, %s, NULL, %s, %s)",
                (purchase_incoming_row.get('product_id'), purchase_incoming_row.get('incoming_qty'), u'G已采未入'))
        return True

    # 刷新预计未提货数、可用库存数、采购未入库数
    def refresh_new(self, cr, uid, context=None):
        self.refresh_possible_changed_qty(cr, uid, context=context)
        self.refresh_future_exchange_qty(cr, uid, context=context)
        self.refresh_available_stock_qty(cr, uid, context=context)
        self.refresh_purchase_incoming_qty(cr, uid, context=context)
        self.summary_today(cr, uid, context=context)
        return True